#!/bin/bash
set -e
set -o pipefail

# 获取脚本路径
current_dir=$(cd $(dirname $0); pwd)
# 导入公共方法
source ${current_dir}/common.sh
# 导入配置文件
source ${current_dir}/config.cnf

# 创建数据目录
function pg_mkdir_data(){
    # 数据目录
    mkdir -p ${pg_dir}/data
    chown -R postgres:postgres ${pg_dir}/data
    chmod 700 ${pg_dir}/data
}

# yum 安装 postgresql
function pg_install(){
    set +e
    comm_is_user_exists "postgres"
    if [ $? -eq 0 ];then
        useradd postgres
        echo 'postgres' | passwd -f --stdin postgres
    fi
    set -e
    if [ $sys_type = "yum" ];then
        comm_print_note "使用yum安装..."
        local pgdg_repo=$(rpm -qa|grep pgdg-redhat-repo|wc -l)
        if [ $pgdg_repo -eq 0 ];then
            yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
        fi
        yum install -y postgresql${pg_version}-server postgresql${pg_version}-contrib libpq5
    else
        comm_print_note "使用apt-get安装..."
        sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
        wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
        sudo apt-get update
        apt-get install -y postgresql-${pg_version}
    fi
}

# 更改systemd的service内的数据目录
function change_pg_systemd(){
    comm_print_note "更改postgresql-$pg_version.service项的数据路径"
    sed -i 's#^Environment=PGDATA.*#Environment=PGDATA='${pg_datadir}'/#g' /usr/lib/systemd/system/postgresql-$pg_version.service
    systemctl daemon-reload
}


# 配置PG环境变量
function pg_set_env(){
    local isenv=$(cat /home/postgres/.bash_profile|grep PGDATA|wc -l)
    if [ $isenv -eq 0 ];then
        echo -e "
export PGDATA=${pg_datadir}
export LANG=en_US.utf8
export PGHOME=$pg_home
export PATH=\$PATH:\$PGHOME/bin
export LD_LIBRARY_PATH=\$PGHOME/lib" >> /home/postgres/.bash_profile
        source /home/postgres/.bash_profile
    fi
}

# 删除PG环境变量
function pg_del_env(){
    comm_print_note "删除PG环境变量..."
    sed -i '/^export PGDATA=.*/d' /home/postgres/.bash_profile
    sed -i '/^export LANG=en_US.utf8/d' /home/postgres/.bash_profile
    sed -i '/^export PGHOME=.*/d' /home/postgres/.bash_profile
    sed -i '/^export PATH=.*.PGHOME.*/d' /home/postgres/.bash_profile
    sed -i '/^export LD_LIBRARY_PATH=.*/d' /home/postgres/.bash_profile
}

function pg_set_config_hba(){
# 放开访问权限
    sed -i '87i host    all             all             0.0.0.0/0               md5' ${pg_datadir}/pg_hba.conf
}

# 修改postgres用户密码
function pg_set_postgres_password(){
  su - postgres -c "psql -c \"alter user postgres with password '${postgres_password}'\""
}

# 获取动态参数值
function pg_get_config_dynamic_parameter(){
    #free_mem=$(free |grep Mem|awk '{print $2}')
    free_mem=${allow_mem_bytes}
    shared_buffers="$(expr ${free_mem} / 1024 / 4)MB"
    maintenance_work_mem="$(expr ${free_mem} / 1024 / 16)MB"
    effective_cache_size="$(expr ${free_mem} / 1024 / 2)MB"
    set +e
    cpu_core=$(expr $(nproc) / 2)
    set -e
    min_wal_size=$(expr ${free_mem} / 8192)
    max_wal_size=$(expr ${free_mem} / 2048)
    autovacuum_work_mem=$(expr ${free_mem} / 64)

    if [ $cpu_core -lt 1 ];then
        max_parallel_maintenance_workers=2
        max_parallel_workers_per_gather=2
    else
        max_parallel_maintenance_workers="$cpu_core"
        max_parallel_workers_per_gather="$cpu_core"
    fi

    if [ ${min_wal_size} -lt 256 ];then
        min_wal_size='256MB'
    elif [ ${min_wal_size} -gt 8192 ];then
        min_wal_size='8GB'
    else
        min_wal_size="${min_wal_size}MB"
    fi

    if [ ${max_wal_size} -lt 2048 ];then
        max_wal_size='2GB'
    elif [ ${max_wal_size} -gt 16384 ];then
        max_wal_size='16GB'
    else
        max_wal_size="${max_wal_size}MB"
    fi

    if [ $autovacuum_work_mem -lt 128 ];then
        autovacuum_work_mem="128MB"
    else
        autovacuum_work_mem="${autovacuum_work_mem}MB"
    fi

    if [ ${is_ssd} -eq 1 ];then
        random_page_cost=1.1
        effective_io_concurrency=200 
    else
        random_page_cost=4
        effective_io_concurrency=2
    fi

    timezone='Asia/Shanghai'
}

# 配置 postgresql.conf
function pg_set_config_postgresql(){   
    mv ${pg_datadir}/postgresql.conf  ${pg_datadir}/postgresql.conf.old
cat > ${pg_datadir}/postgresql.conf <<EOF
# 重要参数
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'
port = ${pg_port}
max_connections = 1024
superuser_reserved_connections = 10
#max_locks_per_transaction = 64
track_commit_timestamp = on
wal_level = replica
wal_log_hints = on
wal_keep_segments = 128
max_wal_senders = 24
max_replication_slots = 16
password_encryption = md5
# $(expr $(free -m|grep Mem|awk '{print $2}') / 4)
shared_buffers = ${shared_buffers}
#huge_pages = try
work_mem = 4MB
maintenance_work_mem = ${maintenance_work_mem}
# 根据CPU核数来算，至少值为2   max(CPU核数/2,2)
# $(expr $(cat /proc/cpuinfo| grep "cpu cores"| uniq|awk '{print $4}')/2)
max_parallel_maintenance_workers = ${max_parallel_maintenance_workers}
# 根据CPU核数来算，至少值为2   max(CPU核数/2,2)
max_parallel_workers_per_gather = ${max_parallel_workers_per_gather}
#max_parallel_workers = max(DBInstanceClassCPU*3/4, 8)
max_parallel_workers = 8
temp_file_limit = 20GB
#vacuum_cost_delay = 20ms
#vacuum_cost_limit = 2000
bgwriter_lru_maxpages = 800
bgwriter_lru_multiplier = 5.0
# 最小256M，最大8G
min_wal_size = ${min_wal_size}
# 最小2G，最大16G
max_wal_size = ${max_wal_size}
wal_buffers = 16MB
wal_writer_delay = 20ms
#wal_writer_flush_after = 1MB
checkpoint_timeout = 15min
archive_mode = on
archive_timeout = 300
archive_command = 'pgbackrest --stanza=${bak_stanza_name} archive-push %p'
#vacuum_defer_cleanup_age = 0
hot_standby = on
max_standby_archive_delay = 10min
max_standby_streaming_delay = 3min
wal_receiver_status_interval = 1s
hot_standby_feedback = on
#wal_receiver_timeout = 60s
#max_logical_replication_workers = 8
# HDD:4   SSD:1.1
random_page_cost = ${random_page_cost}
# HDD:2  SSD:200 
effective_io_concurrency = ${effective_io_concurrency}
effective_cache_size = ${effective_cache_size}

## 时区相关
timezone = '${timezone}'
log_timezone = '${timezone}'

## 日志相关配置
log_destination = csvlog
logging_collector = on
log_truncate_on_rotation  = on
log_filename = 'postgresql-%d.log'
# 当天产生日志超出5G则覆盖重写，也可设置为0不覆盖
log_rotation_size = 500MB
log_rotation_age = 1d
log_checkpoints = on
log_lock_waits = on
log_statement = ddl
log_min_duration_statement = 1000

# 统计信息
track_io_timing = on
track_functions = pl
track_activity_query_size = 4096

# AUTOVACUUM
log_autovacuum_min_duration = 10s
autovacuum_work_mem = ${autovacuum_work_mem}
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_scale_factor = 0.08
autovacuum_analyze_scale_factor = 0.05
#autovacuum_vacuum_cost_delay = -1
#autovacuum_vacuum_cost_limit = -1

idle_in_transaction_session_timeout = 10min
shared_preload_libraries = 'pg_stat_statements'

#auto_explain.log_min_duration = -1
#auto_explain.log_analyze = off
#auto_explain.log_verbose = off
#auto_explain.log_timing = off
#auto_explain.log_nested_statements = t

pg_stat_statements.max = 5000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off
pg_stat_statements.track_planning = off

#timescaledb.telemetry_level = 'off'
#timescaledb.max_background_workers = 16
EOF

chown -R postgres:postgres ${pg_datadir}/postgresql.conf
}

# 初始化
function pg_init(){
    ${pg_home}/bin/postgresql-${pg_version}-setup initdb
}   

# 检查PG是否已准备好提供服务
function pg_isready(){
    local check_value=0
    local count=0
    while [ $count -le 5 ]
    do
      local check_value=$(${pg_home}/bin/pg_isready|grep 'accepting connections'|wc -l)
      if [ $check_value -eq 1 ]; then
        comm_print_note "PostgreSQL is running"
        break
      else
        comm_print_note "Waiting for PostgreSQL to start..."
        sleep 5
        count=$((count+1))
      fi
    done 
    if [ $check_value -eq 0 ]; then
      comm_print_error "PostgreSQL is not running"
      exit 1
    fi
}

# 修改postgres用户密码
function pg_set_postgres_password(){
  su - postgres -c "psql -c \"alter user postgres with password '${postgres_password}'\""
}

# 卸载PG
function uninstall_pg(){
    comm_print_note "卸载postgresql${pg_version}"
    if [ $sys_type = "yum" ];then
        yum remove postgresql${pg_version} -y
    else 
        apt-get –purge remove postgresql${pg_version}
    fi
    comm_print_note "删除数据目录： ${pg_datadir}"
    rm -rf ${pg_datadir}
    comm_print_note "删除postgres用户"
    set +e
    userdel postgres
    set -e
}

# 使用说明
usage () {
        cat <<EOF
Usage: $0 [OPTIONS]
  single                 安装单机版PG,但未配置备份
  pgonly                 仅安装pg，不进行任何配置
  uninstall               清除 PG 软件与数据目录
  isrunning              检查PG是否正常运行且准备好提供服务
EOF
exit
}

# main 入口
command="${1}"
case "${command}" in
    "single" )
        comm_print_note "安装单机版PG..."
        #comm_print_note "执行安装前预检查..."
        #before_install_check
	    comm_print_note "安装postgresql软件,当前安装版本${pg_version}"
        pg_install
        comm_print_note "创建PG数据目录"
        pg_mkdir_data
        comm_print_note "更改systemd的service内的数据目录"
        change_pg_systemd
        comm_print_note "PG初始化"
        pg_init
        comm_print_note "配置hba.conf"
        pg_set_config_hba
        comm_print_note "配置postgresql.conf"
        pg_get_config_dynamic_parameter
        pg_set_config_postgresql
        comm_print_note "配置PG环境变量"
        pg_set_env
        comm_print_note "启动PG"
        systemctl start postgresql-${pg_version}.service
        comm_print_note "查看PG是否已启动可提供服务"
        pg_isready
        comm_print_note "修改postgres用户密码"
        pg_set_postgres_password
    ;;
    "pgonly" )
        comm_print_note "仅安装pg，不进行任何配置"
	    #comm_print_note "执行安装前预检查..."
        #before_install_check
	    comm_print_note "安装postgresql软件,当前安装版本${pg_version}"
        pg_install
        comm_print_note "创建PG数据目录"
        pg_mkdir_data
        comm_print_note "配置PG环境变量"
        pg_set_env
    ;;
    "uninstall" )  
	    comm_print_note "清除 PG 相关环境"
        uninstall_pg
        pg_del_env
    ;;
    "isrunning" )  
	    comm_print_note "检查PG是否正常运行且准备好提供服务"
        pg_isready
    ;;
    * )
        usage
    ;;
esac
